﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Web;

namespace Core
{
	public class SQLiteMessageStorage:IMessageStorage
	{
		public SQLiteMessageStorage()
		{
			m_DbPath = HttpContext.Current.Server.MapPath("~/App_Data/Lesktop.db");

			SQLiteConnection conn = new SQLiteConnection(ConnectionString);
			conn.Open();
			try
			{
				SQLiteCommand cmd = new SQLiteCommand(
					"select max(Key) as MaxKey, max(CreatedTime) as MaxCreatedTime from Message",
					conn
				);

				SQLiteDataReader reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					m_MaxKey = reader[0] == DBNull.Value ? 1 : reader.GetInt64(0);
					m_MaxCreatedTime = reader[1] == DBNull.Value ? DateTime.Now : reader.GetDateTime(1);
				}
			}
			finally
			{
				conn.Close();
			}
		}

		String m_DbPath = "";
		Int64 m_MaxKey = 1;
		DateTime m_MaxCreatedTime = DateTime.Now;

		private string ConnectionString
		{
			get
			{
				return String.Format("Data Source=\"{0}\";Pooling=False", m_DbPath);
			}
		}

		Int64 IMessageStorage.GetMaxKey()
		{
			return m_MaxKey;
		}

		DateTime IMessageStorage.GetCreatedTime()
		{
			return m_MaxCreatedTime;
		}

		List<Message> IMessageStorage.FindHistory(long user, long peer, DateTime from, DateTime to)
		{
			SQLiteConnection conn = new SQLiteConnection(ConnectionString);
			conn.Open();
			try
			{

				if (from == null) from = new DateTime(2000, 1, 1);
				SQLiteCommand cmd;

				cmd = new SQLiteCommand(
					@"
					select temp.Key, temp.Receiver, temp.Sender, temp.Content, temp.CreatedTime 
					from (
						select Key, Receiver, Sender, Content, CreatedTime 
						from Message 
						where Receiver = ? and Sender = ? and CreatedTime >= ? and CreatedTime <= ?

						union

						select Key, Receiver, Sender, Content, CreatedTime 
						from Message 
						where Receiver = ? and Sender = ? and CreatedTime >= ? and CreatedTime <= ?

						union

						select 
							m.Key, m.Receiver, m.Sender, m.Content, m.CreatedTime 
						from 
							Message m, UserRelationShip ur, Users u, Users s
						where 
							ur.HostKey = ? and m.Receiver = ? 
							and ur.GuestKey = u.Key and u.Type = 1 and ur.GuestKey = m.Receiver 
							and CreatedTime >= ? and CreatedTime <= ?
							and m.Sender = s.Key and s.UpperName <> 'ADMINISTRATOR' and m.CreatedTime > ur.RenewTime
					) temp
					order by CreatedTime desc Limit 0, 30",
					conn
				);

				cmd.Parameters.Add("P01", DbType.Int64).Value = user;
				cmd.Parameters.Add("P02", DbType.Int64).Value = peer;
				cmd.Parameters.Add("P03", DbType.DateTime).Value = from;
				cmd.Parameters.Add("P04", DbType.DateTime).Value = to;
				cmd.Parameters.Add("P05", DbType.Int64).Value = peer;
				cmd.Parameters.Add("P06", DbType.Int64).Value = user;
				cmd.Parameters.Add("P07", DbType.DateTime).Value = from;
				cmd.Parameters.Add("P08", DbType.DateTime).Value = to;
				cmd.Parameters.Add("P09", DbType.Int64).Value = user;
				cmd.Parameters.Add("P10", DbType.Int64).Value = peer;
				cmd.Parameters.Add("P11", DbType.DateTime).Value = from;
				cmd.Parameters.Add("P12", DbType.DateTime).Value = to;

				List<Message> messages = new List<Message>();
				SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
				try
				{
					while (reader.Read())
					{
						Message msg = new Message(
							AccountImpl.Instance.GetUserInfo(Convert.ToInt64(reader[2])),
							AccountImpl.Instance.GetUserInfo(Convert.ToInt64(reader[1])),
							reader.GetString(3), Convert.ToDateTime(reader[4]), Convert.ToInt64(reader[0])
						);
						messages.Add(msg);
					}
				}
				finally
				{
					reader.Close();
				}
				return messages;
			}
			finally
			{
				conn.Close();
			}
		}

		List<Message> IMessageStorage.Find(long receiver, long sender, Nullable<DateTime> from)
		{
			SQLiteConnection conn = new SQLiteConnection(ConnectionString);
			conn.Open();
			try
			{

				if (from == null) from = new DateTime(2000, 1, 1);
				SQLiteCommand cmd;
				if (sender != 0)
				{
					cmd = new SQLiteCommand(
						@"
						select Key,Receiver,Sender,Content,CreatedTime
						from Message 
						where Receiver = ? and Sender = ? and CreatedTime > ?
						order by CreatedTime desc Limit 0, 100",
						conn
					);

					cmd.Parameters.Add("Receiver", DbType.Int32).Value = receiver;
					cmd.Parameters.Add("Sender", DbType.Int32).Value = sender;
					cmd.Parameters.Add("CreatedTime", DbType.DateTime).Value = from.Value;
				}
				else
				{
					cmd = new SQLiteCommand(
						@"
						select temp.Key, temp.Receiver, temp.Sender, temp.Content, temp.CreatedTime 
						from (
							select Key, Receiver, Sender, Content, CreatedTime 
							from Message 
							where Receiver = ? and CreatedTime > ?
							union
							select m.Key, m.Receiver, m.Sender, m.Content, m.CreatedTime 
							from Message m, UserRelationShip ur, Users u
							where ur.HostKey = ? and ur.GuestKey = u.Key and u.Type = 1 and ur.GuestKey = m.Receiver and m.CreatedTime > ? and m.CreatedTime > ur.RenewTime
						) temp
						order by CreatedTime desc Limit 0, 100",
						conn
					);

					cmd.Parameters.Add("Receiver", DbType.Int32).Value = receiver;
					cmd.Parameters.Add("CreatedTime", DbType.DateTime).Value = from.Value;
					cmd.Parameters.Add("Receiver2", DbType.Int32).Value = receiver;
					cmd.Parameters.Add("CreatedTime2", DbType.DateTime).Value = from.Value;
				}

				List<Message> messages = new List<Message>();
				SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
				try
				{
					while (reader.Read())
					{
						Message msg = new Message(
							AccountImpl.Instance.GetUserInfo(Convert.ToInt64(reader[2])),
							AccountImpl.Instance.GetUserInfo(Convert.ToInt64(reader[1])),
							reader.GetString(3), Convert.ToDateTime(reader[4]), Convert.ToInt64(reader[0])
						);
						messages.Add(msg);
					}
				}
				finally
				{
					reader.Close();
				}
				return messages;
			}
			finally
			{
				conn.Close();
			}
		}

		void IMessageStorage.Write(List<Message> messages)
		{
			SQLiteConnection conn = new SQLiteConnection(ConnectionString);
			conn.Open();
			try
			{
				//启动事务
				SQLiteTransaction trans = conn.BeginTransaction();

				try
				{
					foreach (Message msg in messages)
					{
						//超过缓存的最大值，将缓存中的消息全部写入数据库
						SQLiteCommand cmd = new SQLiteCommand(
							"insert into Message (Receiver,Sender,Content,CreatedTime,Key) values (?,?,?,?,?)",
							conn
						);
						cmd.Parameters.Add("Receiver", DbType.Int32).Value = msg.Receiver.ID;
						cmd.Parameters.Add("Sender", DbType.Int32).Value = msg.Sender.ID;
						cmd.Parameters.Add("Content", DbType.String).Value = msg.Content;
						cmd.Parameters.Add("CreatedTime", DbType.DateTime).Value = msg.CreatedTime;
						cmd.Parameters.Add("Key", DbType.Int64).Value = msg.Key;
						cmd.Transaction = trans;
						cmd.ExecuteNonQuery();
					}

					trans.Commit();
				}
				catch
				{
					trans.Rollback();
				}
			}
			finally
			{
				conn.Close();
			}
		}
	}
}
